/**
 *                            License
 * THE WORK (AS DEFINED BELOW) IS PROVIDED UNDER THE TERMS OF THIS  
 * CREATIVE COMMONS PUBLIC LICENSE ("CCPL" OR "LICENSE"). 
 * THE WORK IS PROTECTED BY COPYRIGHT AND/OR OTHER APPLICABLE LAW.  
 * ANY USE OF THE WORK OTHER THAN AS AUTHORIZED UNDER THIS LICENSE OR  
 * COPYRIGHT LAW IS PROHIBITED.
 * 
 * BY EXERCISING ANY RIGHTS TO THE WORK PROVIDED HERE, YOU ACCEPT AND  
 * AGREE TO BE BOUND BY THE TERMS OF THIS LICENSE. TO THE EXTENT THIS LICENSE  
 * MAY BE CONSIDERED TO BE A CONTRACT,
 * THE LICENSOR GRANTS YOU THE RIGHTS CONTAINED 
 * HERE IN CONSIDERATION OF YOUR ACCEPTANCE OF SUCH TERMS AND CONDITIONS.
 * 
 */
package lineage.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库抽象类.
 * 
 * @version 2014年3月13日上午11:51:39
 * @author jrwz
 */
public abstract class L1AbstractDatabase {
    private static final Logger LOG = LoggerFactory.getLogger(L1AbstractDatabase.class);

    /**
     * 取得只有单一'整数'的结果.
     * 
     * @param sql
     *            - 要执行的SQL语句
     * @return 查询结果
     */
    protected int getSingleInt(final String sql) {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        int result = 0;

        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con.prepareStatement(sql);
            rs = pstm.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (final SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        } catch (final Exception e) {
            LOG.error(e.getLocalizedMessage(), e);
        } finally {
            close(rs, pstm, con);
        }
        return result;
    }

    /**
     * 取得只有单一'字符串'的结果.
     * 
     * @param sql
     *            - 要执行的SQL语句
     * @return 查询结果
     */
    protected String getSingleString(final String sql) {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        String result = null;

        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con.prepareStatement(sql);
            rs = pstm.executeQuery();
            if (rs.next()) {
                result = rs.getString(1);
            }
        } catch (final SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        } catch (final Exception e) {
            LOG.error(e.getLocalizedMessage(), e);
        } finally {
            close(rs, pstm, con);
        }
        return result;
    }

    /**
     * 取得只有单一'布尔值'的结果.
     * 
     * @param sql
     *            - 要执行的SQL语句
     * @return 查询结果
     */
    protected boolean getSingleBoolean(final String sql) {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        boolean result = false;

        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con.prepareStatement(sql);
            rs = pstm.executeQuery();
            result = rs.next();
        } catch (final SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        } catch (final Exception e) {
            LOG.error(e.getLocalizedMessage(), e);
        } finally {
            close(rs, pstm, con);
        }
        return result;
    }

    /**
     * 批量处理数据.
     * 需要同时重写'fillTable'方法.
     * 
     * @param sql
     *            - 要执行的SQL语句
     */
    protected void batch(final String sql) {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con.prepareStatement(sql);
            rs = pstm.executeQuery();
            fillTable(rs);
        } catch (final SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        } catch (final Exception e) {
            LOG.error(e.getLocalizedMessage(), e);
        } finally {
            close(rs, pstm, con);
        }
    }

    /**
     * batch的内置方法(一般会加循环语句以批量取得数据).
     * 此方法应该被重写.
     * 
     * @param rs
     *            - 结果集
     * @throws SQLException
     */
    protected void fillTable(final ResultSet rs) throws SQLException {
        LOG.info("此方法应该被重写");
    }

    /**
     * 执行'更新'(不返回结果).
     * 
     * @param sql
     *            - 要执行的SQL语句
     */
    protected void update(final String sql) {
        Connection con = null;
        PreparedStatement pstm = null;
        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (final SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        } catch (final Exception e) {
            LOG.error(e.getLocalizedMessage(), e);
        } finally {
            close(pstm, con);
        }
    }

    /**
     * 执行'更新'(返回结果).
     * 
     * @param sql
     *            - 要执行的SQL语句
     * @return 更新成功则返回true
     */
    protected boolean updateResult(final String sql) {
        Connection con = null;
        PreparedStatement pstm = null;

        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (final SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
            return false;
        } catch (final Exception e) {
            LOG.error(e.getLocalizedMessage(), e);
            return false;
        } finally {
            close(pstm, con);
        }
        return true;
    }

    /**
     * 一次关闭三个.
     * 
     * @param rs
     *            - 结果集
     * @param pstm
     *            - 表示预编译的 SQL 语句的对象
     * @param con
     *            - 数据库的连接
     */
    private void close(final ResultSet rs, final PreparedStatement pstm, final Connection con) {
        close(rs);
        close(pstm);
        close(con);
    }

    /**
     * 一次关闭三个.
     * 
     * @param rs
     *            - 结果集
     * @param st
     *            - 操作对象
     * @param con
     *            - 数据库的连接
     */
    protected void close(final ResultSet rs, final Statement st, final Connection con) {
        close(rs);
        close(st);
        close(con);
    }

    /**
     * 一次关闭两个.
     * 
     * @param pstm
     *            - 表示预编译的 SQL 语句的对象
     * @param con
     *            - 数据库的连接
     */
    private void close(final PreparedStatement pstm, final Connection con) {
        close(pstm);
        close(con);
    }

    /**
     * 一次关闭两个.
     * 
     * @param st
     *            - 操作对象
     * @param con
     *            - 数据库的连接
     */
    protected void close(final Statement st, final Connection con) {
        close(st);
        close(con);
    }

    /**
     * 关闭 Connection.
     * 
     * @param con
     *            - 数据库的连接
     * @return null or SQLException
     */
    private void close(final Connection con) {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        }
    }

    /**
     * 关闭 PreparedStatement.
     * 
     * @param pstm
     *            - 表示预编译的 SQL 语句的对象
     * @return null or SQLException
     */
    private void close(final PreparedStatement pstm) {
        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        }
    }

    /**
     * 关闭 Statement.
     * 
     * @param st
     *            - 操作对象
     * @return null or SQLException
     */
    private void close(final Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        }
    }

    /**
     * 关闭 ResultSet.
     * 
     * @param rs
     *            - 结果集
     * @return null or SQLException
     */
    private void close(final ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            LOG.error(e.getLocalizedMessage(), e);
        }
    }
}
